package model;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class LibriImpl implements Libri {

	private Connection conn;

	public LibriImpl(Connection conn) throws SQLException, IOException {
		this.conn = conn;
	}

	public void aggiungiLibro(long isbn, String titolo, String autore,
			String casaEditrice, String genere, int numCopie, String url,
			String posizioneScaffale) throws SQLException {
		String ps = "INSERT into Libri values (?,?,?,?,?,CURDATE(),?,?,?)";
		PreparedStatement stat = conn.prepareStatement(ps);
		stat.setLong(1, isbn);
		stat.setString(2, titolo);
		stat.setString(3, autore);
		stat.setString(4, casaEditrice);
		stat.setString(5, genere);
		stat.setInt(6, numCopie);
		stat.setString(7, url);
		stat.setString(8, posizioneScaffale);
		stat.executeUpdate();
		stat.close();
	}

	public boolean disponibile(long isbn) throws SQLException {
		String ps = "SELECT numCopie from Libri WHERE isbn = ?";
		PreparedStatement stat = conn.prepareStatement(ps);
		stat.setLong(1, isbn);
		ResultSet rs = stat.executeQuery();
		boolean ris = rs.next();
		if (!ris)
			return false;
		int x = rs.getInt("numCopie");
		stat.close();
		if (x > 0)
			return true;
		return false;
	}

	@Override
	public ResultSet cercaLibro(String[] parametri, String[] nomiParametri)
			throws SQLException {
		StringBuilder sb = new StringBuilder(300);
		sb.append("SELECT * FROM Libri where ");
		int c = 0;
		for (int i = 0; i < parametri.length; i++) {
			if ((!parametri[i].equals("null")) && parametri[i].length() > 1) {
				sb.append(nomiParametri[i]);
				sb.append(" LIKE \"%");
				sb.append(parametri[i]);
				sb.append("%\" ");
				sb.append(" AND ");
				c++;
			}
		}
		if (c > 0)
			sb.append(" 1=1");
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery(sb.toString());
		return rs;
	}

	@Override
	public ResultSet getUltimiPresi() throws SQLException {
		String query = "SELECT Libri_isbn from StoricoCliente ORDER BY inizioPrestito DESC";
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery(query);
		return rs;
	}

	@Override
	public ResultSet getInfoLibro(long isbn) throws SQLException {
		String query = "SELECT titolo,autore,url from Libri WHERE isbn = ?";
		PreparedStatement stat = conn.prepareStatement(query);
		stat.setLong(1, isbn);
		ResultSet rs = stat.executeQuery();
		return rs;

	}

	@Override
	public ResultSet getUltimiArrivi() throws SQLException {
		String query = "SELECT titolo,autore,url from Libri ORDER BY dataArrivo DESC";
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery(query);
		return rs;
	}

	@Override
	public void incrementaCopie(long isbn, int idCliente) throws SQLException {
		String ps = "UPDATE Libri SET numCopie = numCopie + 1 WHERE isbn = ?";
		PreparedStatement stat = conn.prepareStatement(ps);
		stat.setLong(1, isbn);
		stat.executeUpdate();
		stat.close();
	}

	@Override
	public void decrementaCopie(long isbn) throws SQLException {
		String ps = "UPDATE Libri SET numCopie = numCopie - 1 WHERE isbn = ?";
		PreparedStatement stat = conn.prepareStatement(ps);
		stat.setLong(1, isbn);
		stat.executeUpdate();
		stat.close();
	}

	@Override
	public int copieLibri(long isbn) throws SQLException {
		String ps = "SELECT numCopie from Libri WHERE isbn = ?";
		PreparedStatement stat = conn.prepareStatement(ps);
		stat.setLong(1, isbn);
		ResultSet rs = stat.executeQuery();
		rs.next();
		return rs.getInt(1);
	}

	@Override
	public void aggiungiLibroStorico(int idCliente, long isbn)
			throws SQLException {
		String ps = "INSERT into StoricoCliente values (0,?,CURDATE(),DATE_ADD(curdate(),INTERVAL 30 day),null,?)";
		PreparedStatement stat = conn.prepareStatement(ps);
		stat.setInt(1, idCliente);
		stat.setLong(2, isbn);

		stat.executeUpdate();
		stat.close();
	}

	@Override
	public void modificaDataEffettiva(int idCliente, long isbn)
			throws SQLException {
		String ps = "UPDATE StoricoCliente SET dataReale=CURDATE() WHERE Cliente_idCliente = ? AND Libri_isbn = ?";
		PreparedStatement stat = conn.prepareStatement(ps);
		stat.setInt(1, idCliente);
		stat.setLong(2, isbn);
		stat.executeUpdate();
		stat.close();

	}

	@Override
	public boolean possoPrendereLibro(int idCliente, long isbn)
			throws SQLException {
		String ps = "SELECT * from StoricoCliente WHERE Cliente_idCliente = ? AND Libri_isbn = ? AND dataReale IS NULL";
		PreparedStatement stat = conn.prepareStatement(ps);
		stat.setInt(1, idCliente);
		stat.setLong(2, isbn);
		ResultSet rs = stat.executeQuery();
		return !rs.next();
	}

	@Override
	public boolean hoIlLibro(int idCliente, long isbn) throws SQLException {

		String ps = "SELECT *  from StoricoCliente WHERE Cliente_idCliente = ? AND Libri_isbn = ? AND dataReale IS NULL";
		PreparedStatement stat = conn.prepareStatement(ps);
		stat.setInt(1, idCliente);
		stat.setLong(2, isbn);
		ResultSet rs = stat.executeQuery();

		return rs.next();

	}

	@Override
	public void aggiornaCopie(int numCopie2, long isbn) throws SQLException {

		String ps = "UPDATE Libri SET numCopie=numCopie+? WHERE isbn=?";
		PreparedStatement stat = conn.prepareStatement(ps);
		stat.setInt(1, numCopie2);
		stat.setLong(2, isbn);

		stat.executeUpdate();
		stat.close();

	}

	@Override
	public boolean esiste(long isbn) throws SQLException {
		String ps = "SELECT *  from Libri WHERE isbn = ?";
		PreparedStatement stat = conn.prepareStatement(ps);
		stat.setLong(1, isbn);
		ResultSet rs = stat.executeQuery();
		return rs.next();
	}

	@Override
	public int numLibri() throws SQLException {
		String query = "SELECT count(*) from Libri";
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery(query);
		rs.next();
		return rs.getInt(1);
	}

}
